﻿using System.Threading.Tasks;
using System;
using Microsoft.EntityFrameworkCore;
using Oracle.ManagedDataAccess.Client;
using Microsoft.AspNetCore.Http;
using Simple.Services.System;
using Simple.Services.System.QHSEUser.Models;
using Simple.Services.System.QHSEViolationCategory.Models;

namespace Simple.Services;

public class ViolationClauseAnalysisService
{
    private readonly SimpleDbContext _context;
    private readonly QHSEXyViolationTypeService _violationTypeService;
    private readonly QHSEKeyWorkSpecialtyService _keyWorkSpecialtyService;
    private readonly QHSEDictionaryService _dictionaryService;
    public ViolationClauseAnalysisService(SimpleDbContext context, QHSEXyViolationTypeService violationTypeService, QHSEKeyWorkSpecialtyService keyWorkSpecialtyService, QHSEDictionaryService dictionaryService)
    {
        _context = context;
        _violationTypeService = violationTypeService;
        _keyWorkSpecialtyService = keyWorkSpecialtyService;
        _dictionaryService = dictionaryService;
    }
    private static string EscapeLikePattern(string pattern)
    {
        return pattern.Replace("'", "''").Replace("/", "//").Replace("_", "/_").Replace("%", "/%");
    }
    public async Task<CompareViolationAnalysisResult> ViolationTypeAnalysisAsync(ViolationAnalysisInput input)
    {
        CompareViolationAnalysisResult result = new CompareViolationAnalysisResult();

        if (input.CheckType.Count != 0 && input.TimeOn != null && input.TimeEnd != null && input.OrganizationName != null)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            var query = _context.Set<QHSESysXyViolation>()
                .Where(u => input.CheckType.Contains(u.Check_Type) && u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                .Join(
                    _context.Set<QHSESysOrganization>(),
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Violation);
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            var violationTypes = await _violationTypeService.GetAsync();
            var clauses = await _dictionaryService.GetDictionaryAsync("1007");

            List<Object> vio = new List<Object> { "违章性质" };
            vio.AddRange(violationTypes.Select(violationType => violationType.ViolationType));
            result.Item.Add(vio);

            foreach (var clause in clauses)
            {
                var violationCounts = await query
                    .Where(u => u.CauseAnalysis == clause.Item)
                    .GroupBy(u => u.ViolationType)
                    .Select(g => new { ViolationType = g.Key, Count = g.Count() })
                    .ToListAsync();

                List<Object> violation = new List<Object> { clause.Item };
                foreach (var violationType in violationTypes)
                {
                    var count = violationCounts.FirstOrDefault(vc => vc.ViolationType == violationType.ViolationType)?.Count ?? 0;
                    violation.Add(count);
                }
                result.Item.Add(violation);
            }
        }

        return result;
    }
    public async Task<CompareViolationAnalysisResult> WorkSpecialityAnalysisAsync(ViolationAnalysisInput input)
    {
        CompareViolationAnalysisResult result = new CompareViolationAnalysisResult();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();

        if (!string.IsNullOrEmpty(input.OrganizationName)
            && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query
                .Where(u => input.CheckType.Contains(u.Check_Type) && u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                .Join(
                    _context.Set<QHSESysOrganization>(),
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Violation);
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            var clauses = await _dictionaryService.GetDictionaryAsync("1007");
            var workSpecialitys = await _keyWorkSpecialtyService.GetAsync();

            List<Object> vio = new List<Object> { "作业专业" };
            vio.AddRange(workSpecialitys.Select(work => work.Specialty));
            result.Item.Add(vio);

            foreach (var clause in clauses)
            {
                List<Object> violation = new List<Object> { clause.Item };
                var violationCounts = await query
                    .Where(u => u.CauseAnalysis == clause.Item)
                    .GroupBy(u => u.WorkSpecialty)
                    .Select(g => new { WorkSpecialty = g.Key, Count = g.Count() })
                    .ToListAsync();

                foreach (var workSpeciality in workSpecialitys)
                {
                    var value = violationCounts.FirstOrDefault(v => v.WorkSpecialty == workSpeciality.Specialty)?.Count ?? 0;
                    violation.Add(value);
                }

                result.Item.Add(violation);
            }
        }

        return result;
    }
    public async Task<CompareViolationAnalysisResult> AgeAnalysisAsync(ViolationAnalysisInput input)
    {
        CompareViolationAnalysisResult result = new CompareViolationAnalysisResult();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();

        if (!string.IsNullOrEmpty(input.OrganizationName)
            && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query
                .Where(u => input.CheckType.Contains(u.Check_Type) && u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                .Join(
                    _context.Set<QHSESysOrganization>(),
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Violation);
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            var clauses = await _dictionaryService.GetDictionaryAsync("1007");
            var ages = await _dictionaryService.GetDictionaryAsync("1002");

            List<Object> vio = new List<Object> { "年龄" };
            vio.AddRange(ages.Select(age => age.Item));
            result.Item.Add(vio);

            foreach (var clause in clauses)
            {
                List<Object> violation = new List<Object> { clause.Item };
                var violationCounts = await query
                    .Where(u => u.CauseAnalysis == clause.Item)
                    .GroupBy(u => u.Age)
                    .Select(g => new { Age = g.Key, Count = g.Count() })
                    .ToListAsync();

                foreach (var age in ages)
                {
                    var value = violationCounts.FirstOrDefault(v => v.Age == age.Item)?.Count ?? 0;
                    violation.Add(value);
                }

                result.Item.Add(violation);
            }
        }

        return result;
    }
    public async Task<CompareViolationAnalysisResult> WorkYearsAnalysisAsync(ViolationAnalysisInput input)
    {
        CompareViolationAnalysisResult result = new CompareViolationAnalysisResult();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();

        if (!string.IsNullOrEmpty(input.OrganizationName)
            && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query
                .Where(u => input.CheckType.Contains(u.Check_Type) && u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                .Join(
                    _context.Set<QHSESysOrganization>(),
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Violation);
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            var clauses = await _dictionaryService.GetDictionaryAsync("1007");
            var workYears = await _dictionaryService.GetDictionaryAsync("1003");

            List<Object> vio = new List<Object> { "工龄" };
            vio.AddRange(workYears.Select(work => work.Item));
            result.Item.Add(vio);

            foreach (var clause in clauses)
            {
                List<Object> violation = new List<Object> { clause.Item };
                var violationCounts = await query
                    .Where(u => u.CauseAnalysis == clause.Item)
                    .GroupBy(u => u.WorkYears)
                    .Select(g => new { WorkYears = g.Key, Count = g.Count() })
                    .ToListAsync();

                foreach (var workYear in workYears)
                {
                    var newitem = "";
                    var value = violationCounts.FirstOrDefault(u => workYear.Item == u.WorkYears)?.Count ?? 0;
                    if (workYear.Item == "1年及以下")
                    {
                        value += violationCounts.FirstOrDefault(u => "1年以下".Contains(u.WorkYears))?.Count ?? 0;
                    }
                    else if (workYear.Item == "2-3年")
                    {
                        value += violationCounts.FirstOrDefault(u => "1-5年".Contains(u.WorkYears))?.Count ?? 0;
                    }
                    else if (workYear.Item == "5年以上")
                    {
                        value += violationCounts.FirstOrDefault(u => "5-10年+10-15年+15年以上".Contains(u.WorkYears))?.Count ?? 0;
                    }
                    violation.Add(value);
                }

                result.Item.Add(violation);
            }
        }

        return result;
    }
    public async Task<CompareViolationAnalysisResult> EmployeeTypeAnalysisAsync(ViolationAnalysisInput input)
    {
        CompareViolationAnalysisResult result = new CompareViolationAnalysisResult();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();

        if (!string.IsNullOrEmpty(input.OrganizationName)
            && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0)
        {
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            var escapedNamePath = EscapeLikePattern(org.NamePath);
            query = query
                .Where(u => input.CheckType.Contains(u.Check_Type) && u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd)
                .Join(
                    _context.Set<QHSESysOrganization>(),
                    violation => violation.OrganizationId,
                    organization => organization.OrganizationId,
                    (violation, organization) => new { Violation = violation, Organization = organization })
                .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                .Select(joinResult => joinResult.Violation);
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            var clauses = await _dictionaryService.GetDictionaryAsync("1007");
            var employeeTypes = await _dictionaryService.GetDictionaryAsync("1004");

            List<Object> vio = new List<Object> { "用工性质" };
            vio.AddRange(employeeTypes.Select(type => type.Item));
            result.Item.Add(vio);

            foreach (var clause in clauses)
            {
                List<Object> violation = new List<Object> { clause.Item };
                var violationCounts = await query
                    .Where(u => u.CauseAnalysis == clause.Item)
                    .GroupBy(u => u.EmployeeType)
                    .Select(g => new { EmployeeType = g.Key, Count = g.Count() })
                    .ToListAsync();

                foreach (var employeeType in employeeTypes)
                {
                    var value = violationCounts.FirstOrDefault(v => v.EmployeeType == employeeType.Item)?.Count ?? 0;
                    violation.Add(value);
                }

                result.Item.Add(violation);
            }
        }

        return result;
    }
}
